﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace Controller
{
    public class CategoryKPIController : Controller.dbConnection.ConnectionClass
    {
        public string Save(CategoryKPI Categories)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_INSERTUPDATECATEGORYKPI";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = Categories.ID;
            command.Parameters.Add("@DESCRIPTION", SqlDbType.VarChar).Value = Categories.Description;
            command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = Categories.isActive;
            command.Parameters.Add("@GROUPID", SqlDbType.Int).Value = Categories.GroupKPI.ID;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = Categories.LastModifiedBy;
            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = Categories.CreateBy;
            command.Parameters.Add("@SEQUENCE", SqlDbType.VarChar).Value = Categories.Sequence;

            return ExecuteNonQuery(command);
        }

        public string Delete(CategoryKPI Categories)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_DELETECATEGORYKPI";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = Categories.ID;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = Categories.LastModifiedBy;

            return ExecuteNonQuery(command);
        }

        public string BeforeDelete(CategoryKPI Categories)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_CheckBeforeDeleteCategoryKPI";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = Categories.ID;
            //command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = Categories.LastModifiedBy;

            return GetSingleData(command);
        }

        public IList<CategoryKPI> GetAll(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllCategoryKPIs";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;

            DataSet dx = GetTable(command);

            List<CategoryKPI> Categories = new List<CategoryKPI>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "Description":
                            dx.Tables[0].DefaultView.Sort = "Description ASC";
                            break;
                        case "GroupKPI":
                            dx.Tables[0].DefaultView.Sort = "GroupID ASC";
                            break;
                        case "LastModifiedDate":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedDate ASC";
                            break;
                        case "LastModifiedBy":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedBy ASC";
                            break;
                        case "CreateDate":
                            dx.Tables[0].DefaultView.Sort = "CreateDate ASC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
                            break;
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "Description":
                            dx.Tables[0].DefaultView.Sort = "Description DESC";
                            break;
                        case "GroupKPI":
                            dx.Tables[0].DefaultView.Sort = "GroupID DESC";
                            break;
                        case "LastModifiedDate":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedDate DESC";
                            break;
                        case "LastModifiedBy":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedBy DESC";
                            break;
                        case "CreateDate":
                            dx.Tables[0].DefaultView.Sort = "CreateDate DESC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
                            break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        CategoryKPI p = new CategoryKPI();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"].ToString();
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        Categories.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return Categories.ToList();
        }

        public IList<CategoryKPI> GetAll()
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllCategoryKPIs";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = string.Empty;

            DataSet dx = GetTable(command);

            List<CategoryKPI> Categories = new List<CategoryKPI>();

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        CategoryKPI p = new CategoryKPI();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"].ToString();
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        Categories.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            return Categories.ToList();
        }


        public IList<CategoryKPI> GetAllCategoryKPIsByGroupKPIID(int GroupKPIID, string sSearch, int PageNumber, int Datalength, int SortCol, string SortDirection, out int TotalData)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllCategoryKPIsByGroupKPIID";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@GROUPID", SqlDbType.Int).Value = GroupKPIID;

            DataSet dx = GetTable(command);

            List<CategoryKPI> Categories = new List<CategoryKPI>();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        CategoryKPI p = new CategoryKPI();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.Description = dx.Tables[0].Rows[i]["Description"].ToString();
                        p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["GroupID"].ToString()));
                        p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        Categories.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dx.Tables[0].Rows.Count;

            return Categories.ToList();
        }

        public IList<CategoryKPI> GetAllCategoryKPIsByGroupKPIID(int GroupKPIID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllCategoryKPIsByGroupKPIID";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = string.Empty;
            command.Parameters.Add("@GROUPID", SqlDbType.Int).Value = GroupKPIID;

            DataSet dx = GetTable(command);

            List<CategoryKPI> Categories = new List<CategoryKPI>();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dx.Tables[0].Rows.Count; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        CategoryKPI p = new CategoryKPI();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.Description = dx.Tables[0].Rows[i]["Description"].ToString();
                        p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["GroupID"].ToString()));
                        p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        Categories.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            return Categories.ToList();
        }

        public CategoryKPI GetCategoryKPIByID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GETCATEGORYKPIBYID";
            command.Parameters.Add("@ID", SqlDbType.Int).Value = ID;

            IList<string> Data = GetSingleRow(command);
            CategoryKPI Categories = null;

            if (Data != null && Data.Count > 0)
            {
                Categories = new CategoryKPI();
                Categories.ID = Convert.ToInt16(Data[0]);
                Categories.Description = Data[1].ToString();
                Categories.isActive = Convert.ToBoolean(Data[2]);
                Categories.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(Data[3]));
                Categories.LastModifiedDate = Data[4] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[4].ToString());
                Categories.LastModifiedBy = Data[5].ToString();
                Categories.CreateDate = Data[6] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[6].ToString());
                Categories.CreateBy = Data[7].ToString();
                Categories.Sequence = Convert.ToInt32(Data[8].ToString());
            }

            return Categories;
        }

        public CategoryKPI GetCategoryKPIByDescription(string Description, int GroupID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GETCATEGORYKPIBYDESCRIPTION";
            command.Parameters.Add("@GROUPID", SqlDbType.Int).Value =GroupID;
            command.Parameters.Add("@DESCRIPTION", SqlDbType.VarChar).Value = Description;

            IList<string> Data = GetSingleRow(command);
            CategoryKPI Categories = null;

            if (Data != null && Data.Count > 0)
            {
                Categories = new CategoryKPI();
                Categories.ID = Convert.ToInt16(Data[0]);
                Categories.Description = Data[1].ToString();
                Categories.isActive = Convert.ToBoolean(Data[2]);
                Categories.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(Data[3]));
                Categories.LastModifiedDate = Data[4] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[4].ToString());
                Categories.LastModifiedBy = Data[5].ToString();
                Categories.CreateDate = Data[6] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[6].ToString());
                Categories.CreateBy = Data[7].ToString();
            }

            return Categories;
        }
    }
}